import java.sql.*;
import java.util.Scanner;

public class jdba {
    static Connection getConn() {
        String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "whn1314";
        Connection conn = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
           conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
return conn;
}
//返回学生数目
    static int  getNum(){
        String sql="select count(*)from ttt";
        ResultSet rs = null;
        Connection conn=getConn();
        PreparedStatement pstmt;
        int i=0;
        int count = 0;
        try{
           pstmt=conn.prepareStatement(sql);
           rs=pstmt.executeQuery();
           if(rs.next()){
               count=rs.getInt(1);
               i=pstmt.executeUpdate();
               System.out.println("学生的数量为："+count);
           }

       } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
}
    //增加
    static String insert() {
        Student student=new Student();
        Scanner s = new Scanner(System.in);
        System.out.println("----请输入学生信息---");
        System.out.print("学号：");
        String ID = s.next();
        System.out.print("姓名：");
        System.out.println("请输入性别 男/女：");
        String sex = s.next();
        System.out.print("年龄：");
        int old = s.nextInt();
        System.out.print("成绩：");
        float score = s.nextFloat();
        String name = s.next();
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into ttt (ID,name,sex,Age,score) values(?,?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1,student.getID());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getSex());
            pstmt.setInt(4, student.getAge());
            pstmt.setFloat(5,student.getScore());
            i = pstmt.executeUpdate();

            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(i==1)
            return "增加成功\n";
        else
            return "增加失败\n";
    }
    //删除
      static  String delete() {
          Scanner s = new Scanner(System.in);
          System.out.print("请输入要删除的学生的学号：");
          String ID=s.next();
        Connection conn = getConn();
        int i = 0;
        String sql = "delete from ttt where ID='" + ID + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("result: " + i);
            pstmt.close();
            conn.close();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        if(i==1)
        {
            return "删除成功\n";
        }
        else
            return "删除失败\n";
    }
    static int update(Student student ){
        Connection conn=getConn();
        int i=0;
        String sql="update ttt set ";
        return i;
    }
    //按ID查找
    static  Integer IDlook(){
        Connection conn = getConn();
        int i=0;
        System.out.print("请输入要查找学生的学号：");
        Scanner s=new Scanner(System.in);
        String ID=s.next();
        String sql = "select ID,name,sex,old,score * from ttt where ID like id";
        PreparedStatement pstmt;
        try{
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i=pstmt.executeUpdate();
            ResultSet rs=pstmt.executeQuery();
            int col=rs.getMetaData().getColumnCount();
            for( i=1;i<=col;i++) {
                System.out.print(rs.getString(i) + "\t");
            }
            System.out.println("");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    static  Integer namelook(){
        Connection conn = getConn();
        int i=0;
        System.out.print("请输入要查找学生的姓名：");
        Scanner s=new Scanner(System.in);
        String name1=s.next();
        String sql = "select ID,name,sex,old,score * from ttt where name like name1 values name1";
        PreparedStatement pstmt;
        try{
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i=pstmt.executeUpdate();
            ResultSet rs=pstmt.executeQuery();
            int col=rs.getMetaData().getColumnCount();
            for( i=1;i<=col;i++) {
                System.out.print(rs.getString(i) + "\t");
            }
            System.out.println("");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
       return  null;
    }
    //绩点排序
    static  Integer getScore(){
        Connection conn = getConn();
        int i;
        String sql = "select * from ttt order by score desc ";
        PreparedStatement pstmt;
        try{
            pstmt=(PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs=pstmt.executeQuery();
            int col=rs.getMetaData().getColumnCount();
            System.out.println("=====================================");
            while (rs.next()) {
                for( i=1;i<=col;i++){
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 5) && (rs.getString(i).length() < 10)) {
                        System.out.println("\t");
                    }
                }
                System.out.println("");
            }
            System.out.println("======================================");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    //学生信息
   static  Integer getAll(){
        Connection conn = getConn();
        int i;
        String sql = "select * from ttt";
        PreparedStatement pstmt;
        try{
            pstmt=(PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs=pstmt.executeQuery();
            int col=rs.getMetaData().getColumnCount();
            System.out.println("=====================================");
            while (rs.next()) {
                for( i=1;i<=col;i++){
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 5) && (rs.getString(i).length() < 10)) {
                        System.out.println("\t");
                    }
                }
                System.out.println("");
            }
            System.out.println("======================================");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
